package com.vertigrated.db.sql99;

import com.vertigrated.temporal.DateUtil;
import com.vertigrated.temporal.TimeStamp;
import com.vertigrated.text.CASE;
import org.testng.annotations.Test;

import java.util.ArrayList;
import java.util.List;

public class TestSelectBuilder
{
    @Test(expectedExceptions = RuntimeException.class)
    public void testEmptyColumn()
    {
        final SelectBuilder ssb = new SelectBuilder("schema", "tablename");
        ssb.getCommand();
    }

    @Test
    public void testNoWhereClause()
    {
        final SelectBuilder ssb = new SelectBuilder("tablename");
        ssb.addColumnName("col1");
        ssb.addColumnName("col2");
        ssb.addColumnName("col3");
        assert "SELECT col1, col2, col3 FROM tablename".equals(ssb.getCommand());
    }

    @Test
    public void testColumnAlias()
    {
        final SelectBuilder ssb = new SelectBuilder("tablename");
        ssb.addColumnNameAlias("col1", "c1");
        assert "SELECT col1 as c1 FROM tablename".equals(ssb.getCommand());
    }

    @Test
    public void testSchemaTableName()
    {
        final SelectBuilder ssb = new SelectBuilder("schema", "tablename");
        ssb.addColumnName("col1");
        assert "SELECT col1 FROM schema.tablename".equals(ssb.getCommand()) : ssb.getCommand();
    }

    @Test
    public void testBasicWhereClause()
    {
        final SelectBuilder ssb = new SelectBuilder("tablename");
        ssb.addColumnName("col1");
        ssb.addWhereEqual("col1", "SOME_DATA");
        assert "SELECT col1 FROM tablename WHERE col1 = 'SOME_DATA'".equals(ssb.getCommand()) : ssb.getCommand();
    }

    @Test
    public void testCaseInsensitveWhereClause()
    {
        final SelectBuilder ssb = new SelectBuilder("tablename");
        ssb.addColumnName("col1");
        ssb.addWhereEqual("col1", "SOME_DATA", CASE.SENSITIVE);
        assert "SELECT col1 FROM tablename WHERE col1 = 'SOME_DATA'".equals(ssb.getCommand()) : ssb.getCommand();
    }

    @Test
    public void testWhereInStringSensitiveClause()
    {
        final List<String> list = new ArrayList<String>(3);
        list.add("A");
        list.add("B");
        list.add("C");
        final SelectBuilder ssb = new SelectBuilder("tablename");
        ssb.addColumnName("col1");
        ssb.addWhereIn("col1", list, CASE.SENSITIVE);
        assert "SELECT col1 FROM tablename WHERE col1 IN ('A','B','C')".equals(ssb.getCommand()) : ssb.getCommand();
    }

    @Test
    public void testWhereInStringInSensitiveClause()
    {
        final List<String> list = new ArrayList<String>(3);
        list.add("A");
        list.add("B");
        list.add("C");
        final SelectBuilder ssb = new SelectBuilder("tablename");
        ssb.addColumnName("col1");
        ssb.addWhereIn("col1", list, CASE.INSENSITIVE);
        assert "SELECT col1 FROM tablename WHERE lower(col1) IN ('a','b','c')".equals(ssb.getCommand()) : ssb.getCommand();
    }

    @Test
    public void testWhereInIntegerClause()
    {
        final List<Number> list = new ArrayList<Number>(3);
        list.add(1);
        list.add(2);
        list.add(3);
        final SelectBuilder ssb = new SelectBuilder("tablename");
        ssb.addColumnName("col1");
        ssb.addWhereIn("col1", list);
        assert "SELECT col1 FROM tablename WHERE col1 IN (1,2,3)".equals(ssb.getCommand()) : ssb.getCommand();
    }

    @Test
    public void testOrderByAscClause()
    {
        final SelectBuilder ssb = new SelectBuilder("tablename");
        ssb.addColumnName("col1");
        ssb.addOrderByAscending("col1");
        assert "SELECT col1 FROM tablename ORDER BY col1 ASC".equals(ssb.getCommand()) : ssb.getCommand();
    }

    @Test
    public void testOrderByDescClause()
    {
        final SelectBuilder ssb = new SelectBuilder("tablename");
        ssb.addColumnName("col1");
        ssb.addOrderByDescending("col1");
        assert "SELECT col1 FROM tablename ORDER BY col1 DESC".equals(ssb.getCommand()) : ssb.getCommand();
    }

    @Test
    public void testBetweenNumberWhereClause()
    {
        final SelectBuilder ssb = new SelectBuilder("tablename");
        ssb.addColumnName("col1");
        ssb.addWhereBetween("col1", Integer.MIN_VALUE, Integer.MAX_VALUE);
        assert "SELECT col1 FROM tablename WHERE col1 BETWEEN -2147483648 AND 2147483647".equals(ssb.getCommand()) : ssb.getCommand();
    }

    @Test
    public void testBetweenDateWhereClause()
    {
        final SelectBuilder ssb = new SelectBuilder("tablename");
        ssb.addColumnName("col1");
        ssb.addWhereBetween("col1", new TimeStamp(DateUtil.BEGINNING_OF_TIME), new TimeStamp(DateUtil.END_OF_TIME));
        assert "SELECT col1 FROM tablename WHERE col1 BETWEEN '0001-12-31T19:00:00.000-05:00' AND '292278994-08-17T02:12:55.8:07-0500'".equals(ssb.getCommand()) : ssb.getCommand();
    }
}
